package com.idlecode.config.query;

import cn.hutool.core.util.StrUtil;
import com.idlecode.config.DbType;

/**
 * PostgreSql查询
 */
public class PostgreSqlQuery implements AbstractQuery {

    @Override
    public DbType dbType() {
        return DbType.PostgreSQL;
    }

    @Override
    public String dbNamesSql() {
        return "SELECT datname AS OWNER FROM pg_database where datistemplate=false ORDER BY datname";
    }

    @Override
    public String tableSql(String dataBaseName, String tableName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\tt1.tablename,\n" +
                "\tobj_description ( relfilenode, 'pg_class' ) AS comments\n" +
                "FROM\n" +
                "\tpg_tables t1,\n" +
                "\tpg_class t2 \n" +
                "WHERE\n" +
                "\tt1.tablename NOT LIKE'pg%' \n" +
                "\tAND t1.tablename NOT LIKE'sql_%' \n" +
                "\tAND t1.tablename = t2.relname ");
        // 表名查询
        if (StrUtil.isNotBlank(tableName)) {
            sql.append("and t1.tablename = '").append(tableName).append("' ");
        }
        return sql.toString();
    }

    @Override
    public String tableFieldsSql(String dataBaseName, String tableName) {
        return StrUtil.format("SELECT\n" +
                "    t2.attname AS columnName,\n" +
                "    pg_type.typname AS dataType,\n" +
                "    col_description(t2.attrelid, t2.attnum) AS columnComment,\n" +
                "    (CASE t3.contype WHEN 'p' THEN 'PRI' ELSE '' END) AS columnKey,\n" +
                "    col.numeric_precision,\n" +
                "    col.numeric_scale\n" +
                "FROM\n" +
                "    pg_class AS t1\n" +
                "    INNER JOIN pg_attribute AS t2 ON t2.attrelid = t1.OID\n" +
                "    INNER JOIN pg_type ON pg_type.OID = t2.atttypid\n" +
                "    LEFT JOIN pg_constraint t3 ON t2.attnum = t3.conkey[1] AND t2.attrelid = t3.conrelid\n" +
                "    LEFT JOIN information_schema.columns col ON col.table_name = t1.relname \n" +
                "                                               AND col.column_name = t2.attname\n" +
                "WHERE\n" +
                "    t1.relname = '{}'\n" +
                "    AND t2.attnum > 0;", tableName);
    }

    @Override
    public String dbName() {
        return "OWNER";
    }

    @Override
    public String tableName() {
        return "tablename";
    }

    @Override
    public String tableComment() {
        return "comments";
    }

    @Override
    public String tableUpdateTime() {
        return "update_time";
    }

    @Override
    public String fieldName() {
        return "columnName";
    }

    @Override
    public String fieldType() {
        return "dataType";
    }

    @Override
    public String fieldComment() {
        return "columnComment";
    }

    @Override
    public String fieldKey() {
        return "columnKey";
    }

    @Override
    public String dataPrecision() {
        return "numeric_precision";
    }

    @Override
    public String dataScale() {
        return "numeric_scale";
    }
}
